package com.laf.manager.repository;

import com.laf.manager.controller.dto.MemberCarDto;
import com.laf.manager.controller.dto.PointsDeductionDto;
import com.laf.manager.dto.MemberCar;
import com.laf.manager.querycondition.MemberCarQueryCondition;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ygl
 * @create 2018-12-26
 * @DESCRIPTION
 **/
@Repository
@Slf4j
public class MemberCarRepository {

    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 通过 会员ID 查询 会员绑定的车辆列表
     * @param memberId
     * @return
     */
    public List<MemberCar> findByMemberId(Integer memberId){
        String sql = "SELECT * FROM t_member_car WHERE member_id = ?";
        List<MemberCar> list = jdbcTemplate.query(sql,new Object[]{memberId},new MemberCar());
        return list;
    }


    /**
     * 通过 会员ID 和 车牌号 删除数据
     * @param memberId
     * @param carNumber
     * @return
     */
    public int deleteByMemberIdAndCarNumber(Integer memberId,String carNumber){
        String sql = "DELETE FROM t_member_car WHERE member_id=? and car_number=?";
        return jdbcTemplate.update(sql, memberId,carNumber);
    }


    public int deleteById(Integer id) {
        String sql = "DELETE FROM t_member_car WHERE id=?";
        return jdbcTemplate.update(sql, id);
    }

    public List<MemberCarDto> getList(MemberCarQueryCondition condition) {
        String sql = "SELECT c.*,m.name as member_name FROM t_member_car c left join t_member m on c.member_id = m.member_id where 1=1 ";

        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> args = new ArrayList<>();


        if (!StringUtils.isEmpty(condition.getMemberName())) {
            sb.append(" AND m.name like '%' ? '%'");
            args.add(condition.getMemberName().trim());
        }

        if (!StringUtils.isEmpty(condition.getCarNumber())) {
            sb.append(" AND c.car_number like '%' ? '%'");
            args.add(condition.getCarNumber().trim());
        }

        sb.append(" ORDER BY"); // 排序默认按排序号排序
        sb.append(" " + condition.getSort());
        sb.append(" " + condition.getDirection());

        sb.append(" LIMIT ?, ?");
        args.add(condition.getOffset());
        args.add(condition.getSize());

        log.debug("offset is {}", condition.getOffset());
        log.debug("size is {}", condition.getSize());

        log.debug("args: {}", args.toString());
        log.debug("sql: {}", sb.toString());

        List<MemberCarDto> list = jdbcTemplate.query(sb.toString(), args.toArray(), new MemberCarDto());
        return list;
    }

    public int getCountList(MemberCarQueryCondition condition) {
        String sql = "SELECT count(1) as count FROM t_member_car c left join t_member m on c.member_id = m.member_id where 1=1 ";

        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> args = new ArrayList<>();
        ArrayList<Integer> argsType = new ArrayList<>();

        if (!StringUtils.isEmpty(condition.getMemberName())) {
            sb.append(" AND m.name like '%' ? '%'");
            args.add(condition.getMemberName().trim());
            argsType.add(Types.VARCHAR);
        }

        if (!StringUtils.isEmpty(condition.getCarNumber())) {
            sb.append(" AND c.car_number like '%' ? '%'");
            args.add(condition.getCarNumber().trim());
            argsType.add(Types.VARCHAR);
        }

        int[] arr = new int[argsType.size()];
        for (int i = 0; i < arr.length; i++) {
            arr[i] = argsType.get(i);
        }

        log.debug("args: {}", args.toString());
        log.debug("sql: {}", sb.toString());

        SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sb.toString(), args.toArray(), arr);
        rowSet.first();

        return rowSet.getInt("count");
    }
}
